using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class SSRFANVDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procSSRFANV_add]";
        private string _strSPUpdateName = "dbo.[procSSRFANV_update]";
        private string _strSPDeleteName = "dbo.[procSSRFANV_delete]";
        private string _strSPGetName = "dbo.[procSSRFANV_get]";
        private string _strSPGetAllName = "dbo.[procSSRFANV_getall]";
        private string _strSPGetPages = "dbo.[procSSRFANV_getpaged]";
        private string _strSPIsExist = "dbo.[procSSRFANV_isexist]";
        private string _strTableName = "SSRFANV";
        private string _strSPGetAllNameBU = "dbo.[procSSRFANV_getallBU]";
        #endregion Local Variable

        #region Method
        public SSRFANVInfo Get_SSRFANV(
            String SUN_DB
        , ref string sErr)
        {
            SSRFANVInfo objEntr = new SSRFANVInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("SUN_DB", SUN_DB);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = (SSRFANVInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            SSRFANVInfo result = new SSRFANVInfo();
            result.SUN_DB = (dt.Rows[i]["SUN_DB"] == DBNull.Value ? "" : (String)dt.Rows[i]["SUN_DB"]);
            result.CATEGORY = (dt.Rows[i]["CATEGORY"] == DBNull.Value ? "" : (String)dt.Rows[i]["CATEGORY"]);
            result.CODE = (dt.Rows[i]["CODE"] == DBNull.Value ? "" : (String)dt.Rows[i]["CODE"]);
            result.LOOKUP = (dt.Rows[i]["LOOKUP"] == DBNull.Value ? "" : (String)dt.Rows[i]["LOOKUP"]);
            result.UPDATED = (dt.Rows[i]["UPDATED"] == DBNull.Value ? "" : (String)dt.Rows[i]["UPDATED"]);
            result.NAME = (dt.Rows[i]["NAME"] == DBNull.Value ? "" : (String)dt.Rows[i]["NAME"]);
            result.PROHB_POST = (dt.Rows[i]["PROHB_POST"] == DBNull.Value ? "" : (String)dt.Rows[i]["PROHB_POST"]);
            result.BUDGET_CHECK = (dt.Rows[i]["BUDGET_CHECK"] == DBNull.Value ? "" : (String)dt.Rows[i]["BUDGET_CHECK"]);
            result.BUDGET_STOP = (dt.Rows[i]["BUDGET_STOP"] == DBNull.Value ? "" : (String)dt.Rows[i]["BUDGET_STOP"]);
            result.DATA_1 = (dt.Rows[i]["DATA_1"] == DBNull.Value ? "" : (String)dt.Rows[i]["DATA_1"]);

            return result;
        }

        public DataTable GetAll_SSRFANV(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAll_SSRFANVBU(String CATEGORY, String SUN_DB, ref string sErr, string connectString)
        {

            //InitSPCommand(_strSPIsExistHVJ);
            string sql = @"SELECT SUN_DB,CATEGORY,ltrim(rtrim(CODE)) CODE,LOOKUP,UPDATED,NAME,PROHB_POST,BUDGET_CHECK,BUDGET_STOP,DATA_1, 
                    case when isnull(rtrim(ltrim(CODE)),'') = '' then ''
                    else isnull(rtrim(ltrim(CODE)),'') + ' - ' + isnull(rtrim(ltrim(NAME)),'')
                    end as CODENAME
                FROM  SSRFANV
                WHERE SUN_DB = @SUN_DB AND CATEGORY = @CATEGORY AND 'Y' <> PROHB_POST";
            //AddParameter("TREFERENCE", TREFERENCE);

            if (connectString == null) connectString = ConnectionString;

            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlParameter p = command.CreateParameter(); p.ParameterName = "SUN_DB"; p.Value = SUN_DB;
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CATEGORY"; p.Value = CATEGORY;
            command.Parameters.Add(p);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex) { sErr = ex.Message; }
            return dt;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_SSRFANV(SSRFANVInfo objEntr, ref string sErr, string connect = null)
        {
            int ret = -1;

            string query = "INSERT INTO SSRFANV (SUN_DB,CATEGORY,CODE,UPDATED,NAME,LOOKUP,PROHB_POST,BUDGET_CHECK,BUDGET_STOP,DATA_1) values(" + "'" + objEntr.SUN_DB + "'"
                 + ",'" + objEntr.CATEGORY + "'" + ",'" + objEntr.CODE.Replace("'", "''") + "'" + ",'" + objEntr.UPDATED + "'" + ",'" + objEntr.NAME.Replace("'", "''") + "'"
                 + ",'" + objEntr.LOOKUP + "'" + ",'" + objEntr.PROHB_POST + "'" + ",'" + objEntr.BUDGET_CHECK + "'" + ",'" + objEntr.BUDGET_STOP + "'" + ",'" + objEntr.DATA_1 + "')";


            if (connect == null) connect = ConnectionString;

            SqlConnection conn = new SqlConnection(connect);
            SqlCommand command = new SqlCommand(query, conn);
            conn.Open();
            try
            {
                command.ExecuteNonQuery();
                ret = 1;
            }
            catch (Exception ex) { sErr = ex.Message; }
            finally { conn.Close(); }
            return ret;
        }

        public string Update_SSRFANV(SSRFANVInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("SUN_DB", objEntr.SUN_DB);
            AddParameter("CATEGORY", objEntr.CATEGORY);
            AddParameter("CODE", objEntr.CODE);
            AddParameter("LOOKUP", objEntr.LOOKUP);
            AddParameter("UPDATED", objEntr.UPDATED);
            AddParameter("NAME", objEntr.NAME);
            AddParameter("PROHB_POST", objEntr.PROHB_POST);
            AddParameter("BUDGET_CHECK", objEntr.BUDGET_CHECK);
            AddParameter("BUDGET_STOP", objEntr.BUDGET_STOP);
            AddParameter("DATA_1", objEntr.DATA_1);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_SSRFANV(
            String SUN_DB
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("SUN_DB", SUN_DB);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist_SSRFANV(String SUN_DB, String CATEGORY, String CODE, string connectString = null)
        {
            //InitSPCommand(_strSPIsExistHVJ);
            string sql = @"SELECT TOP (1) CODE
                FROM SSRFANV 
                WHERE SUN_DB = @SUN_DB  and CATEGORY = @CATEGORY and CODE = @CODE";
            //AddParameter("TREFERENCE", TREFERENCE);
            if (connectString == null) connectString = ConnectionString;

            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlParameter p = command.CreateParameter(); p.ParameterName = "SUN_DB"; p.Value = SUN_DB;
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CATEGORY"; p.Value = CATEGORY;
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CODE"; p.Value = CODE;
            command.Parameters.Add(p);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            string sErr = "";
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex) { sErr = ex.Message; }
            return dt.Rows.Count > 0;

        }

        private string CreateWhereClause(SSRFANVInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
